﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using FX.CC.Andon.Entity.BasicDataManage;
using FX.CC.Andon.Utility.Common;
using FX.CC.Andon.Utility.DataBase;

namespace FX.CC.Andon.Service.BasicDataManage
{
    /// <summary>
    /// 异常分类管理数据层
    /// </summary>
    public class ETypeDetailService : DbHelperBase
    {
        /// <summary>
        /// 添加异常分类管理
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddETypeDetail(ETypeDetailEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [UT_ETypeDetail]([DetailID],[CatID],[DetailCode],[DetailName],[DetailDesc],[RecStatus]) ");
            strSql.Append(" VALUES(@DetailID,@CatID,@DetailCode,@DetailName,@DetailDesc,@RecStatus) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@DetailID", model.DetailID);
                    Db.AddInParameter(cmd, "@CatID", model.CatID);
                    Db.AddInParameter(cmd, "@DetailCode", model.DetailCode);
                    Db.AddInParameter(cmd, "@DetailName", model.DetailName);
                    Db.AddInParameter(cmd, "@DetailDesc", model.DetailDesc);
                    Db.AddInParameter(cmd, "@RecStatus", true);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
        /// <summary>
        /// 修改异常分类管理
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool ModifyETypeDetail(ETypeDetailEntity model)
        {

            bool result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [UT_ETypeDetail] SET [DetailCode] = @DetailCode,[DetailName] = @DetailName,[DetailDesc] = @DetailDesc WHERE [DetailID] = @DetailID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@DetailCode", model.DetailCode);
                    Db.AddInParameter(cmd, "@DetailName", model.DetailName);
                    Db.AddInParameter(cmd, "@DetailDesc", model.DetailDesc);
                    Db.AddInParameter(cmd, "@DetailID", model.DetailID);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
        /// <summary>
        /// 获取异常分类管理单条信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ETypeDetailEntity GetETypeDetailInfo(string id)
        {
            var model = new ETypeDetailEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [DetailID],[CatID],[DetailCode],[DetailName],[DetailDesc],[RecStatus] FROM [UT_ETypeDetail] where [DetailID]=@DetailID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                Db.AddInParameter(cmd, "@DetailID", id);
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["DetailID"] != DBNull.Value)
                                model.DetailID = new Guid(reader["DetailID"].ToString());
                            if (reader["CatID"] != DBNull.Value)
                                model.CatID = new Guid(reader["CatID"].ToString());
                            model.DetailCode = reader["DetailCode"] == obj ? null : Convert.ToString(reader["DetailCode"]);
                            model.DetailName = reader["DetailName"] == obj ? null : Convert.ToString(reader["DetailName"]);
                            model.DetailDesc = reader["DetailDesc"] == obj ? null : Convert.ToString(reader["DetailDesc"]);
                            if (reader["RecStatus"] != DBNull.Value)
                                model.RecStatus = Convert.ToBoolean(reader["RecStatus"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }


        /// <summary>
        /// 获取总记录数
        /// </summary>
        /// <param name="qcmodel"></param>
        /// <returns></returns>
        public int GetETypeDetailCount(ETypeDetailQcEntity qcmodel)
        {
            var count = 0;
            var strSql = new StringBuilder();
            strSql.Append(" select  count(*) AS [count] FROM UT_ETypeDetail  WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.RecStatus))
            {
                strSql.Append(" and RecStatus=" + qcmodel.RecStatus);
            }
            if (!string.IsNullOrEmpty(qcmodel.CatID))
            {
                strSql.Append(" and CatID='" + qcmodel.CatID + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.DetailCode))
            {
                strSql.Append(" and DetailCode like '" + qcmodel.DetailCode + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.DetailName))
            {
                strSql.Append(" and DetailName like '" + qcmodel.DetailName + "%'");
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        /// <summary>
        /// 获取异常分类管理列表分页
        /// </summary>
        /// <param name="qcmodel"></param>
        /// <returns></returns>
        public List<ETypeDetailEntity> GetETypeDetailList(ETypeDetailQcEntity qcmodel)
        {
            var list = new List<ETypeDetailEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            int start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            int end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by InputTime) AS idx,[DetailID],[CatID],[DetailCode],[DetailName],[DetailDesc],[RecStatus] FROM [UT_ETypeDetail] where 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.RecStatus))
            {
                strSql.Append(" and RecStatus=" + qcmodel.RecStatus);
            }
            if (!string.IsNullOrEmpty(qcmodel.CatID))
            {
                strSql.Append(" and CatID='" + qcmodel.CatID + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.DetailCode))
            {
                strSql.Append(" and DetailCode like '" + qcmodel.DetailCode + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.DetailName))
            {
                strSql.Append(" and DetailName like '" + qcmodel.DetailName + "%'");
            }
            strSql.Append(" ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new ETypeDetailEntity();
                            if (reader["DetailID"] != DBNull.Value)
                                model.DetailID = new Guid(reader["DetailID"].ToString());
                            if (reader["CatID"] != DBNull.Value)
                                model.CatID = new Guid(reader["CatID"].ToString());
                            model.DetailCode = reader["DetailCode"] == obj ? null : Convert.ToString(reader["DetailCode"]);
                            model.DetailName = reader["DetailName"] == obj ? null : Convert.ToString(reader["DetailName"]);
                            model.DetailDesc = reader["DetailDesc"] == obj ? null : Convert.ToString(reader["DetailDesc"]);
                            if (reader["RecStatus"] != DBNull.Value)
                                model.RecStatus = Convert.ToBoolean(reader["RecStatus"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public List<ETypeDetailEntity> GetETypeDetailList(string id)
        {
            var list = new List<ETypeDetailEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT [DetailID],[CatID],[DetailCode],[DetailName],[DetailDesc],[RecStatus] FROM [UT_ETypeDetail] where CatID=@CatID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@CatID", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new ETypeDetailEntity();
                            if (reader["DetailID"] != DBNull.Value)
                                model.DetailID = new Guid(reader["DetailID"].ToString());
                            if (reader["CatID"] != DBNull.Value)
                                model.CatID = new Guid(reader["CatID"].ToString());
                            model.DetailCode = reader["DetailCode"] == obj ? null : Convert.ToString(reader["DetailCode"]);
                            model.DetailName = reader["DetailName"] == obj ? null : Convert.ToString(reader["DetailName"]);
                            model.DetailDesc = reader["DetailDesc"] == obj ? null : Convert.ToString(reader["DetailDesc"]);
                            if (reader["RecStatus"] != DBNull.Value)
                                model.RecStatus = Convert.ToBoolean(reader["RecStatus"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public void ImportETypeDetail(List<ETypeDetailEntity> list)
        {
            var conn = Db.GetConnection() as SqlConnection;
            if (conn == null)
            {
                throw new InvalidOperationException("No Valid Database Connection!");
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (var bcp = new SqlBulkCopy(conn))
            {
                var mapDetailId =
                    new SqlBulkCopyColumnMapping("DetailID", "DetailID");
                bcp.ColumnMappings.Add(mapDetailId);

                var mapCatId =
                    new SqlBulkCopyColumnMapping("CatID", "CatID");
                bcp.ColumnMappings.Add(mapCatId);

                var mapDetailCode =
                    new SqlBulkCopyColumnMapping("DetailCode", "DetailCode");
                bcp.ColumnMappings.Add(mapDetailCode);

                var mapDetailName =
                   new SqlBulkCopyColumnMapping("DetailName", "DetailName");
                bcp.ColumnMappings.Add(mapDetailName);

                var mapDetailDesc =
                   new SqlBulkCopyColumnMapping("DetailDesc", "DetailDesc");
                bcp.ColumnMappings.Add(mapDetailDesc);

                var mapRecStatus =
                   new SqlBulkCopyColumnMapping("RecStatus", "RecStatus");
                bcp.ColumnMappings.Add(mapRecStatus);

                bcp.DestinationTableName = "UT_ETypeDetail";

                try
                {
                    bcp.WriteToServer(Collection.ToDataTable(list));
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }
        }

        public void UpdateETypeDetail(List<ETypeDetailEntity> list)
        {
            using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT DetailID,CatID, DetailCode,DetailName,DetailDesc,RecStatus INTO [#DetailTmp] FROM UT_ETypeDetail Where 1=2";
                    cmd.ExecuteNonQuery();
                }

                using (var bcp = new SqlBulkCopy(conn))
                {
                    var mapDetailId =
                        new SqlBulkCopyColumnMapping("DetailID", "DetailID");
                    bcp.ColumnMappings.Add(mapDetailId);

                    var mapCatId =
                        new SqlBulkCopyColumnMapping("CatID", "CatID");
                    bcp.ColumnMappings.Add(mapCatId);

                    var mapDetailCode =
                        new SqlBulkCopyColumnMapping("DetailCode", "DetailCode");
                    bcp.ColumnMappings.Add(mapDetailCode);

                    var mapDetailName =
                        new SqlBulkCopyColumnMapping("DetailName", "DetailName");
                    bcp.ColumnMappings.Add(mapDetailName);

                    var mapDetailDesc =
                        new SqlBulkCopyColumnMapping("DetailDesc", "DetailDesc");
                    bcp.ColumnMappings.Add(mapDetailDesc);

                    var mapRecStatus =
                        new SqlBulkCopyColumnMapping("RecStatus", "RecStatus");
                    bcp.ColumnMappings.Add(mapRecStatus);

                    bcp.DestinationTableName = "#DetailTmp";

                    try
                    {
                        bcp.WriteToServer(Collection.ToDataTable(list));
                    }
                    catch
                    {
                        throw;
                    }
                }

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText =
    @"Update A Set A.EqID=B.EqID, A.PlanOutput=B.PlanOutput
From #DetailTmp B Inner Join UT_PrdTaskDetail A ON A.RowID=B.RowID;
Drop Table #DetailTmp;";
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public IList<ExportETypeDetailEntity> ExportETypeDetail(ETypeDetailQcEntity qcmodel)
        {
            var list = new List<ExportETypeDetailEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT [DetailCode],[DetailName],[DetailDesc],[RecStatus] FROM [UT_ETypeDetail] where 1=1 ");

            if (!string.IsNullOrEmpty(qcmodel.CatID))
            {
                strSql.Append(" and CatID='" + qcmodel.CatID + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.DetailCode))
            {
                strSql.Append(" and DetailCode like '" + qcmodel.DetailCode + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.DetailName))
            {
                strSql.Append(" and DetailName like '" + qcmodel.DetailName + "%'");
            }

            strSql.Append(" ORDER BY InputTime ");

            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new ExportETypeDetailEntity();
                            model.DetailCode = reader["DetailCode"] == obj ? null : Convert.ToString(reader["DetailCode"]);
                            model.DetailName = reader["DetailName"] == obj ? null : Convert.ToString(reader["DetailName"]);
                            model.DetailDesc = reader["DetailDesc"] == obj ? null : Convert.ToString(reader["DetailDesc"]);
                            var status = false;
                            if (reader["RecStatus"] != DBNull.Value)
                            {
                                status = Convert.ToBoolean(reader["RecStatus"]);
                            }
                            model.RecStatus = status ? "已启用" : "已禁用";
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
    }
}
